wikibot
| Table | Sales.Individual |
| Description | Demographic data about customers that purchase Adventure Works products online. |
Columns ¶
| Column | Data Type | Nullable | Default | Description |
| CustomerID | int | not null | | Unique customer identification number. Foreign key to Customer.CustomerID. |
| ContactID | int | not null | | Identifies the customer in the Contact table. Foreign key to Contact.ContactID. |
| Demographics | xml | null | | Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis. |
| ModifiedDate | datetime | not null | (getdate()) | Date and time the record was last updated. |
Primary Key
| Primary Key | Columns |
| PK_Individual_CustomerID | CustomerID |
Indexes
| Index | Type | Columns |
| PXML_Individual_Demographics | | Demographics |
| XMLPATH_Individual_Demographics | | Demographics |
| XMLPROPERTY_Individual_Demographics | | Demographics |
| XMLVALUE_Individual_Demographics | | Demographics |
Foreign Keys
Triggers
| Trigger | Type |
| iuIndividual | ON INSERT UPDATE |
Trigger iuIndividual
CREATE TRIGGER [Sales].[iuIndividual] ON [Sales].[Individual]
AFTER INSERT, UPDATE NOT FOR REPLICATION AS
BEGIN
DECLARE @Count int;
SET @Count = @@ROWCOUNT;
IF @Count = 0
RETURN;
SET NOCOUNT ON;
-- Only allow the Customer to be a Store OR Individual
IF EXISTS (SELECT * FROM inserted INNER JOIN [Sales].[Store]
ON inserted.[CustomerID] = [Sales].[Store].[CustomerID])
BEGIN
-- Rollback any active or uncommittable transactions
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
END;
IF UPDATE([CustomerID]) OR UPDATE([Demographics])
BEGIN
UPDATE [Sales].[Individual]
SET [Sales].[Individual].[Demographics] = N'
0.00
'
FROM inserted
WHERE [Sales].[Individual].[CustomerID] = inserted.[CustomerID]
AND inserted.[Demographics] IS NULL;
UPDATE [Sales].[Individual]
SET [Demographics].modify(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
insert 0.00
as first
into (/IndividualSurvey)[1]')
FROM inserted
WHERE [Sales].[Individual].[CustomerID] = inserted.[CustomerID]
AND inserted.[Demographics] IS NOT NULL
AND inserted.[Demographics].exist(N'declare default element namespace
"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey";
/IndividualSurvey/TotalPurchaseYTD') <> 1;
END;
END;
References
Dependencies